/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Controllers;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import Models.Account;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;

/**
 *
 * @author TranDangThuan
 */
public class AccountController {
    public static Account login(String user, String pass){
        Connection conn = DBUtility.openConnection();
        if (conn != null) {
            try {
                PreparedStatement ps = conn.prepareStatement("Select * from Accounts where AccSerialNo=? and AccPassword=?");
                ps.setString(1, user);
                ps.setString(2, pass);
                ResultSet rs = ps.executeQuery();
                if(rs.next()) {
                   return GetAccountByID(rs.getInt("AccID"));
                }
            } catch (Exception ex) {
                Logger.getLogger(AccountController.class.getName()).log(Level.SEVERE, null, ex);
            } finally {
            }
        }
        return null;
    }
    
    public static String getPermission(String user, String pass){
        Connection conn = DBUtility.openConnection();
        if (conn != null) {
            try {
                PreparedStatement ps = conn.prepareStatement("Select OfficerRole from Accounts where AccSerialNo=? and AccPassword=?");
                ps.setString(1, user);
                ps.setString(2, pass);
                ResultSet rs = ps.executeQuery();
                rs.next();
                String role = rs.getString(1);
                DBUtility.closeConnection(conn);
                return role;
            } catch (SQLException ex) {
                Logger.getLogger(AccountController.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return null;
    }
    
    public static Account getUserProfile(String serial, String pass) {
        Connection conn = DBUtility.openConnection();
        Account profile = new Account();
        if (conn != null) {
            try {
                CallableStatement cs = conn.prepareCall("{call sp_GetCurrentUserBySerialAndPass(?, ?)}");
                cs.setString("Serial", serial);
                cs.setString("Pass", pass);
                ResultSet rs = cs.executeQuery();
                if (rs.next()) {
                    profile.setID(rs.getInt("AccID"));
                    profile.setSerialNo(serial);
                    profile.setPassword(rs.getString("AccPassword"));
                    profile.setPicture(rs.getString("AccPicture"));
                    profile.setFullName(rs.getString("OfficerFullName"));
                    profile.setGender(rs.getBoolean("OfficerGender"));
                    profile.setRank(rs.getString("OfficerRank"));
                    profile.setRole(rs.getString("OfficerRole"));
                    profile.setDepartment(rs.getString("Department"));
                    profile.setRegion(rs.getString("Region"));
                    profile.setAddress(rs.getString("OfficerAddress"));
                    profile.setDOB(rs.getDate("OfficerDOB"));
                    profile.setPhoneNo(rs.getString("OfficerPhone"));
                    profile.setRetired(rs.getBoolean("IsRetired"));
                }
                DBUtility.closeConnection(conn);
            } catch (SQLException ex) {
                Logger.getLogger(AccountController.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return profile;
    }
        static Connection conn = DBUtility.openConnection();

    public static ArrayList<Account> GetAccounts() {
        try {
            ArrayList<Account> accountList = new ArrayList<Account>();

            CallableStatement cs = conn.prepareCall("{call sp_GetAccounts}", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = cs.executeQuery();
            if (rs.next()) {
                rs.beforeFirst();
                while (rs.next()) {
                    int _ID = rs.getInt("AccID");
                    accountList.add(GetAccountByID(_ID));
                }
                return accountList;
            }
        } catch (SQLException ex) {
            Logger.getLogger(AccountController.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public static Account GetAccountByID(int ID) {
        try {
            CallableStatement cs = conn.prepareCall("{call sp_GetAccountByID(?)}");
            cs.setInt(1, ID);
            ResultSet rs = cs.executeQuery();
            if (rs.next()) {
                Account account = new Account();
                account.setID(rs.getInt("AccID"));
                account.setSerialNo(rs.getString("AccSerialNo"));
                account.setPassword(rs.getString("AccPassword"));
                account.setPicture(rs.getString("AccPicture"));
                account.setFullName(rs.getString("OfficerFullName"));
                account.setGender(rs.getBoolean("OfficerGender"));
                account.setRank(rs.getString("OfficerRank"));
                account.setRole(rs.getString("OfficerRole"));
                account.setDepartment(rs.getString("Department"));
                account.setRegion(rs.getString("Region"));
                account.setAddress(rs.getString("OfficerAddress"));
                account.setDOB(rs.getDate("OfficerDOB"));
                account.setPhoneNo(rs.getString("OfficerPhone"));
                account.setRetired(rs.getBoolean("IsRetired"));

                return account;
            }
        } catch (SQLException ex) {
            Logger.getLogger(AccountController.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public static boolean AddAccount(Account account) {
        CallableStatement cs;
        try {
            cs=conn.prepareCall("{call sp_AddAccount(?,?,?,?,?,?,?,?,?,?,?)}");

            cs.setString("AccSerialNo", account.getSerialNo());
            cs.setString("AccPicture", account.getPicture());
            cs.setString("OfficerFullName", account.getFullName());
            cs.setBoolean("OfficerGender", account.isGender());
            cs.setString("OfficerRank", account.getRank());
            cs.setString("OfficerRole", account.getRole());
            cs.setString("Department", account.getDepartment());
            cs.setString("Region", account.getRegion());
            cs.setString("OfficerAddress", account.getAddress());
            java.sql.Date _sqlDate = new java.sql.Date(account.getDOB().getTime());
            cs.setDate("OfficerDOB", _sqlDate);
            cs.setString("OfficerPhone", account.getPhoneNo());

            if (cs.executeUpdate() > 0) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(AccountController.class.getName()).log(Level.SEVERE, null, ex);
        }
        return true;
    }

    public static boolean EditAccount(Account account) {
        CallableStatement cs;
        try {
            cs=conn.prepareCall("{call sp_EditAccount(?,?,?,?,?,?,?,?,?)}");

            cs.setString("AccPicture", account.getPicture());
            cs.setString("OfficerRank", account.getRank());
            cs.setString("OfficerRole", account.getRole());
            cs.setString("Department", account.getDepartment());
            cs.setString("Region", account.getRegion());
            cs.setString("OfficerAddress", account.getAddress());
            cs.setString("OfficerPhone", account.getPhoneNo());
            cs.setBoolean("IsRetired", account.isRetired());
            cs.setInt("AccID", account.getID());

            if (cs.executeUpdate() > 0) {
                return true;
            } else {
                return false;
            }
        } catch (SQLException ex) {
            Logger.getLogger(AccountController.class.getName()).log(Level.SEVERE, null, ex);
        }
        return true;
    }

    public static ArrayList<Account> FilterAccount(HashMap parametersMap) {
        CallableStatement cs;
        Date _date = null;
        ArrayList<Account> resultList = new ArrayList<Account>();
        try {
            cs=conn.prepareCall("{call sp_FilterAccount(?,?,?,?,?,?,?,?,?,?,?,?)}", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

            cs.setString("AccSerialNo", parametersMap.get("AccSerialNo").toString());
            cs.setString("OfficerFullName", parametersMap.get("OfficerFullName").toString());
            cs.setString("OfficerGender", parametersMap.get("OfficerGender").toString());
            cs.setString("OfficerRank", parametersMap.get("OfficerRank").toString());
            cs.setString("OfficerRole", parametersMap.get("OfficerRole").toString());
            cs.setString("Department", parametersMap.get("Department").toString());
            cs.setString("Region", parametersMap.get("Region").toString());
            cs.setString("OfficerAddress", parametersMap.get("OfficerAddress").toString());
            _date = (Date)  parametersMap.get("MinOfficerDOB");
            cs.setDate("MinOfficerDOB", new java.sql.Date(_date.getTime()));
            _date = (Date)  parametersMap.get("MaxOfficerDOB");
            cs.setDate("MaxOfficerDOB", new java.sql.Date(_date.getTime()));
            cs.setString("OfficerPhone", parametersMap.get("OfficerPhone").toString());
            cs.setString("IsRetired", parametersMap.get("IsRetired").toString());

            ResultSet rs = cs.executeQuery();
            if (rs.next()) {
                rs.beforeFirst();
                while (rs.next()) {
                    int _id = rs.getInt("AccID");
                    resultList.add(GetAccountByID(_id));
                }
                return resultList;
            }
        } catch (SQLException ex) {
            Logger.getLogger(AccountController.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }
}
